package JDBC;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import SwingModel.CmsAnnouncement;

public class StudentAnnouncementActivities {

	public static List<CmsAnnouncement> getListOfAnnouncement(String username, String courseID){
		int announcementID;
		String announcementTitle;
		String announcementContent;
		Date createdDTM;
		boolean isRead;
		List<CmsAnnouncement> listOfAnnouncements = new ArrayList<CmsAnnouncement>();
		
		try{
			//Database Connection and Retrieve data
			CmsDatabaseConnection dCon = new CmsDatabaseConnection();
			PreparedStatement pstmt = dCon.getPreparedStatement();
			pstmt = dCon.connection.prepareStatement("SELECT A.AnnounId, A.AnnounTitle, A.AnnounContent, "
					+ "A.CreatedDTM, U.IsRead FROM Announcements A, User_Announ U WHERE A.CourseId = ? "
					+ "AND U.Username = ? AND A.AnnounId = U.AnnounId GROUP BY A.AnnounId DESC");
			
			pstmt.setString(1, courseID);
			pstmt.setString(2, username );
			
			ResultSet resultSet = pstmt.executeQuery();
			
			while(resultSet.next()){
				//get announcement information
				announcementID = resultSet.getInt("AnnounID");
				announcementTitle = resultSet.getString("AnnounTitle");
				announcementContent = resultSet.getString("AnnounContent");
				createdDTM = resultSet.getTimestamp("CreatedDTM");
				isRead = resultSet.getBoolean("IsRead");

				//add announcement entry to list of announcements
				listOfAnnouncements.add(new CmsAnnouncement(announcementID, announcementTitle, announcementContent, createdDTM, isRead));
			}
		} catch(Exception ex){System.out.println(ex);}
		
		return listOfAnnouncements;
	}
	
	public static void setRead(String userID, int anmID){
		CmsDatabaseConnection dCon = new CmsDatabaseConnection();
		PreparedStatement pstmt5 = dCon.getPreparedStatement();
		try {
			pstmt5 = dCon.connection.prepareStatement("UPDATE User_Announ SET IsRead = 1 WHERE AnnounId = ? AND Username = ?;");
			pstmt5.setInt(1, anmID);
			pstmt5.setString(2, userID);
			pstmt5.execute();
			} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("Error: " + e.getMessage().toString());
		}
		
		dCon.close();
	}
	
}
